<?php
/** Error reporting */
	error_reporting(E_ALL);
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/London');

	if (PHP_SAPI == 'cli')
		die('This example should only be run from a Web Browser');

	/** Include PHPExcel */
	require_once 'protected/extensions/PHPExcel.php';

	// Create new PHPExcel object
	//$objPHPExcel = new PHPExcel();

	// Load file
	$objPHPExcel = PHPExcel_IOFactory::load('protected/views/site/pages/PCCoHuu-Mau2-2015-04-16.xlsx');

	// Khởi tạo STT và Lastrow
	$stt=1;
	$lastrow=14;
	$batdau=14;
	$namhoc=(string)Yii::app()->request->cookies['year'];
	$arr_start=array();
	$arr_last=array();
	$count_arr=0;
	
	// Định dạng
	$style_center = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );
	$style_bold = array(
    'font'  => array(
        'bold'  => true,
    ));
	
	// Add some data
	/*
	$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$lastrow, '1')
            ->setCellValue('B'.$lastrow, 'Nguyễn Bá Phương')
            ->setCellValue('C'$lastrow, '1993')
            ->setCellValue('D'.$lastrow, 'GS');*/

	// Truy cập CSDL, lấy thông tin, ghi vào file
		//Lấy tất cả danh sách giảng viên thuộc khoa phụ trách
		
		$connection=Yii::app()->db;
		$command=$connection->createCommand("SELECT * FROM `giangvien` WHERE ((`giangvien`.`magv` in (SELECT `magvchinh` FROM `phancong` WHERE `magvchinh`!='')) OR (`giangvien`.`magv` in (SELECT `magvphu` FROM `phancong` WHERE `magvphu`!=''))) AND `madonvi`='".$_POST['khoaphutrach1']."';")->query();
		if (count($command))
		{
		foreach ($command as $row)
		{			
			// Tìm tất cả các nhóm lớp mà GV tham gia dạy (GV giữ vai trò chính lẫn vai trò phụ)
			$command2=$connection->createCommand("SELECT `phancong`.* FROM `phancong` WHERE ((`magvchinh`='".$row['magv']."') OR (`magvphu`='".$row['magv']."')) AND `phancong`.`maNhom` in (SELECT `nhomlop`.`maNhom` FROM `nhomlop` where `nhomlop`.`namhoc`='".$namhoc."') order by `phancong`.`maNhom` ASC;")->query();
			if (count($command2)>0)
			{
				$tengv=$row['tengv'];
				$namsinh=$row['namsinh'];
				$chucdanhhocvi=$row['chucdanhhocvi'];
				$tiet=0;
				$tongtiet=0;
				$lopdh=0;
				$lopcd=0;
				$loptc=0;
				$lop1=0;
				$lop2=0;
				$lop3=0;
				
				$arr=array();
				$number=0;
				
				foreach ($command2 as $row2)
				{
					$arr[$number]=$row2;
					//echo($arr[$number]['maNhom']);
					$number=$number+1;
				}
				$temp;
				$temp2;
				for ($i=0;$i<$number;$i++)
				{
					$command3=$connection->createCommand("SELECT * FROM `nhomlop` where `maNhom`='".$arr[$i]['maNhom']."';")->query();
					foreach ($command3 as $row3)
					{
						$temp=$row3;
						break;
					}
					
					$command3=$connection->createCommand("SELECT * FROM `monhoc` where `mamh`='".$temp['mamh']."';")->query();
					foreach ($command3 as $row3)
					{
						$temp2=$row3;
						break;
					}
					
					// Tính toán 
						//$giong=1;
						// Tính số lượng lớp ĐH, CĐ, TC
						if (substr($temp['makhoilop'],0,3)=="CTO")
							$lopcd=$lopcd+1;
						else if ((substr($temp['makhoilop'],0,3)=="DTO")||(substr($temp['makhoilop'],0,3)=="DTU"))
							$lopdh=$lopdh+1;
						else
							$loptc=$loptc+1;
						
						// Tính số lượng lớp theo học kỳ 1,2,3
						if ($temp['hocki']==1)
							$lop1=$lop1+1;
						if ($temp['hocki']==2)
							$lop2=$lop2+1;
						if ($temp['hocki']==3)
							$lop3=$lop3+1;
						// Tính số tiết
						if ($arr[$i]['magvchinh']==$row['magv'])
							$tiet=$tiet+$arr[$i]['sotietgvchinh'];
						else
							$tiet=$tiet+$arr[$i]['sotietgvphu'];
						
						/*$objPHPExcel->setActiveSheetIndex(0)
							->setCellValue('Q1', $lopdh)
							->setCellValue('R1', $lopcd)
							->setCellValue('S1', $loptc)
							->setCellValue('T1', $lop1)
							->setCellValue('U1', $lop2)
							->setCellValue('V1', $lop2)
							->setCellValue('W1', $tiet);*/

					if (($i==($number-1))||(substr($arr[$i]['maNhom'],0,6)!=substr($arr[$i+1]['maNhom'],0,6))) // dòng tiếp theo không giống, nên ghi ra FILE luôn và reset các thông số
					{
						$tongtiet=$tongtiet+$tiet;
						if ($lopdh==0)
							$lopdh='';
						if ($lopcd==0)
							$lopcd='';
						if ($loptc==0)
							$loptc='';
						if ($lop1==0)
							$lop1='';
						if ($lop2==0)
							$lop2='';
						if ($lop3==0)
							$lop3='';
						$objPHPExcel->getActiveSheet()->insertNewRowBefore($lastrow,1);
						
						$objPHPExcel->setActiveSheetIndex(0)
							->setCellValue('E'.$lastrow, $temp2['tenmh'])
							->setCellValue('F'.$lastrow, $temp2['mamh'])
							->setCellValue('G'.$lastrow, $temp2['soTC'])
							->setCellValue('H'.$lastrow, ($temp2['sotietLT']+$temp2['sotietTH']))
							->setCellValue('I'.$lastrow, $lopdh)
							->setCellValue('J'.$lastrow, $lopcd)
							->setCellValue('K'.$lastrow, $loptc)
							->setCellValue('L'.$lastrow, $lop1)
							->setCellValue('M'.$lastrow, $lop2)
							->setCellValue('N'.$lastrow, $lop3)
							->setCellValue('O'.$lastrow, $tiet);
							
						$lastrow=$lastrow+1;
						// reset các thông số
						$tiet=0;
						$lopdh=0;
						$lopcd=0;
						$loptc=0;
						$lop1=0;
						$lop2=0;
						$lop3=0;
					}
				}
				
				// Tính tổng tiết
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($lastrow,1);
				
				//$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A14:A20");
				
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A".$batdau.":A".$lastrow);
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("B".$batdau.":"."B".$lastrow);
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("C".$batdau.":"."C".$lastrow);
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("D".$batdau.":"."D".$lastrow);
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("E".$lastrow.":"."N".$lastrow);
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("P".$batdau.":"."P".($lastrow-1));
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells("Q".$batdau.":"."Q".($lastrow-1));
				
				$command5=$connection->createCommand("SELECT * FROM `pccongtackhac` where `magv`='".$row['magv']."';")->query();
				$ctk=0;
				$nd='';
				if (count($command5))
				{
					foreach($command5 as $rw)
					{
						$command6=$connection->createCommand("SELECT * FROM `congtackhac` where `maCTK`='".$rw['maCTK']."';")->query();
						foreach ($command6 as $r)
						{
							$nd=$nd.', '.$r['tenCTK'];
							if ($r['ghichu']!='')
							{
								$nd=$nd.', '.$r['ghichu'];
							}
							$ctk=$ctk+$r['quydoithanhtiet'];
						}
					}
				}
				
				$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('P'.$batdau, $nd)
					->setCellValue('O'.$lastrow, $tongtiet)
					->setCellValue('P'.$lastrow, $ctk)
					->setCellValue('Q'.$lastrow, ($tongtiet+$ctk));
				if ($chucdanhhocvi==0)
				{
					$chucdanhhocvi='Cử nhân';
				}
				if ($chucdanhhocvi==1)
				{
					$chucdanhhocvi='Thạc sĩ';
				}
				if ($chucdanhhocvi==2)
				{
					$chucdanhhocvi='Tiến sĩ';
				}
				if ($chucdanhhocvi==3)
				{
					$chucdanhhocvi='PGS. Tiến sĩ';
				}
				if ($chucdanhhocvi==4)
				{
					$chucdanhhocvi='GS. Tiến sĩ';
				}
				$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('E'.$lastrow, "Tổng");
					
				// Thông tin phân công công tác khác
				// Chưa có công tác khác.

				$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A'.$batdau, $stt)
					->setCellValue('B'.$batdau, $tengv)
					->setCellValue('C'.$batdau, $namsinh)
					->setCellValue('D'.$batdau, $chucdanhhocvi);
				$arr_start[$count_arr]=$batdau;
				$arr_last[$count_arr]=$lastrow;
				$count_arr++;
				$lastrow=$lastrow+1;
				$stt=$stt+1;
				$batdau=$lastrow;
			}
		}
		
		for ($i=0;$i<$count_arr;$i++)
		{
			$lastrow=$arr_last[$i];
			$objPHPExcel->getActiveSheet()->getStyle('E'.$lastrow)->applyFromArray($style_bold);
			$objPHPExcel->getActiveSheet()->getStyle('E'.$lastrow)->applyFromArray($style_center);
			$objPHPExcel->getActiveSheet()->getStyle('O'.($lastrow))->applyFromArray($style_bold);
			$objPHPExcel->getActiveSheet()->getStyle('O'.($lastrow))->applyFromArray($style_center);
			$objPHPExcel->getActiveSheet()->getStyle('P'.($lastrow))->applyFromArray($style_bold);
			$objPHPExcel->getActiveSheet()->getStyle('P'.($lastrow))->applyFromArray($style_center);
			$objPHPExcel->getActiveSheet()->getStyle('Q'.($lastrow))->applyFromArray($style_bold);
			$objPHPExcel->getActiveSheet()->getStyle('Q'.($lastrow))->applyFromArray($style_center);
		}
		}
	
	
	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Simple');

	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);

	// save file
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('test.xls');

	// Redirect output to a client’s web browser (Excel2007)
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="PCGiangVienCoHuu.xlsx"');
	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	echo '<script type="text/javascript">setTimeout("window.close();", 3000);</script>';
	die;exit;
?>